/* 
 * Copyright 2014-2021 UME Framework, GNU General Public License 
 */
package com.u2framework.lib.tool.exp.table.parser;

import java.io.File;
import java.io.FileFilter;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.util.ArrayList;
import java.util.HashMap;
import java.util.LinkedHashMap;
import java.util.List;
import java.util.Map;

import javax.sql.DataSource;

import org.apache.poi.ss.usermodel.BorderStyle;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.FillPatternType;
import org.apache.poi.ss.usermodel.IndexedColors;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.serializer.SerializerFeature;
import com.u2framework.lib.core.util.StringUtil;
import com.u2framework.lib.tool.exp.table.bean.ColumnDescBean;
import com.u2framework.lib.tool.exp.table.bean.TableStructExportBean;
import com.u2framework.lib.tool.exp.table.bean.TableStructExportParam;
import com.u2framework.lib.tool.exp.table.exporter.TableDataExporter;
import com.u2framework.lib.tool.exp.table.exporter.TableStructExporter;
import com.u2framework.lib.tool.exp.table.exporter.TableStructExporter.ColumnExtractMode;
import com.u2framework.lib.tool.poi.CellWriter;
import com.u2framework.lib.tool.poi.ExcelAccessor;
import com.u2framework.lib.tool.poi.SimpleCellWriter;
import com.zaxxer.hikari.HikariConfig;
import com.zaxxer.hikari.HikariDataSource;

/**
 * 1.根据Excel输入的Table列表信息及数据库连接信息，查询数据字典获取表定义的结构， 以此结构创建Excel的页并导出数据库中的既存数据至Excel文件。 <br>
 * 2.根据上述模版结构编辑数据，将编辑后的数据生成可更新至数据库的SQL。<br>
 * 
 * @author SultansOfSwing & MaYue
 */
public class ExportExcelParser extends ExcelAccessor {
    /**
     * logger
     */
    protected Logger logger = LoggerFactory.getLogger(ExportExcelParser.class);

    /**
     * TableStructExporter 实例
     */
    private TableStructExporter tableStructExporter;
    /**
     * TableDataExporter 实例
     */
    private TableDataExporter tableDataExporter;

    /**
     * 构造函数
     */
    public ExportExcelParser(String databaseType) {
        this.tableStructExporter = new TableStructExporter(databaseType);
        this.tableDataExporter = new TableDataExporter(databaseType);
    }

    /**
     * 导出表数据至Excel文件。
     * 
     * @param inputPath
     * @throws Throwable
     */
    public void expTableData(String inputPath) throws Throwable {
        for (File file : getExcelFiles(inputPath)) {
            String excelFile = file.getAbsolutePath();
            Workbook book = this.loadExcel(excelFile);
            DataSource ds = this.getDataSource(book);
            this.expTableDataByFile(book, excelFile, ds);
            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * 根据Excel数据生成 Insert SQL 文件。
     * 
     * @param inputPath
     * @throws Throwable
     */
    public void expTableDataGenInsertSql(String inputPath) throws Throwable {
        for (File file : getExcelFiles(inputPath)) {
            String excelFile = file.getAbsolutePath();
            Workbook book = this.loadExcel(excelFile);
            this.createInsertSqlFile(book, excelFile);
            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * 根据Excel数据生成 JSON 文件。
     * 
     * @param inputPath
     * @param useCamelCase
     * @throws Throwable
     */
    public void expTableDataToJson(String inputPath, Boolean useCamelCase) throws Throwable {
        for (File file : getExcelFiles(inputPath)) {
            String excelFile = file.getAbsolutePath();
            Workbook book = this.loadExcel(excelFile);
            this.readExcelDataAsJson(book, excelFile, useCamelCase);
            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * 导出表结构至Excel。
     * 
     * @param inputPath
     * @throws Throwable
     */
    public void expTableStruct(String inputPath) throws Throwable {
        for (File file : getExcelFiles(inputPath)) {
            String excelFile = file.getAbsolutePath();
            Workbook book = this.loadExcel(excelFile);
            DataSource ds = this.getDataSource(book);

            Map<String, TableStructExportBean> tableExpOutputBeanMap = expTableStructByFile(book, ds);
            for (Map.Entry<String, TableStructExportBean> entry : tableExpOutputBeanMap.entrySet()) {
                String table = entry.getKey();
                TableStructExportBean tableExpOutputBean = entry.getValue();
                try {
                    createTableDocument(book, excelFile, table, tableExpOutputBean.getColumnDescList());
                } catch (Exception e) {
                    logger.error(e.getMessage(), e);
                }
                logger.info("Table [" + table + "] information create.");
            }
            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * 导出表结构至JSON文件。
     * 
     * @param inputPath
     * @throws Throwable
     */
    public void expTableStructToJson(String inputPath) throws Throwable {

        for (File file : getExcelFiles(inputPath)) {
            String excelFile = file.getAbsolutePath();
            Workbook book = this.loadExcel(excelFile);
            DataSource ds = this.getDataSource(book);
            Map<String, TableStructExportBean> tableExpOutputBeanMap = expTableStructByFile(book, ds);

            String jsonFileName = excelFile.substring(0, excelFile.lastIndexOf(".")) + ".json";
            String charset = "UTF-8";
            OutputStreamWriter jsonFilePrinter = null;

            try {
                jsonFilePrinter = new OutputStreamWriter(new FileOutputStream(jsonFileName), charset);
                for (Map.Entry<String, TableStructExportBean> entry : tableExpOutputBeanMap.entrySet()) {
                    TableStructExportBean bean = entry.getValue();
                    bean.setRowList(null);
                    bean.setTableSize(null);
                    for (ColumnDescBean columnDesc : bean.getColumnDescList()) {
                        columnDesc.setSchema(null);
                        columnDesc.setTable(null);
                        columnDesc.setTableComment(null);
                    }
                }

                String jsonStr = JSON.toJSONString(tableExpOutputBeanMap, SerializerFeature.PrettyFormat);
                // String jsonStr = new JsonMapper().writeValueAsString(tableExpOutputBeanMap);

                jsonFilePrinter.write(jsonStr);
                jsonFilePrinter.flush();

            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            } finally {
                jsonFilePrinter.close();
            }

            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * 导出表结构至Excel(所有表字段导出到一页中)
     * 
     * @param inputPath
     * @throws Throwable
     */
    public void expTableStructInOnePage(String inputPath) throws Throwable {
        for (File file : getExcelFiles(inputPath)) {
            String excelFile = file.getAbsolutePath();
            Workbook book = this.loadExcel(excelFile);
            DataSource ds = this.getDataSource(book);

            Map<String, TableStructExportBean> tableExpOutputBeanMap = expTableStructByFile(book, ds);
            List<ColumnDescBean> allColumnDescList = new ArrayList<>();
            for (TableStructExportBean element : tableExpOutputBeanMap.values()) {
                allColumnDescList.addAll(element.getColumnDescList());
            }
            try {
                this.createTableDocumentInOneSheet(book, excelFile, tableStructExporter.getTalleStructInOnePageName(), allColumnDescList);
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * 导出表数据件数至Excel(导出到一页中)
     * 
     * @param inputPath
     * @throws Throwable
     */
    public void expTableRecoirdSizeInOnePage(String inputPath) throws Throwable {
        for (File file : getExcelFiles(inputPath)) {
            String excelFile = file.getAbsolutePath();
            Workbook book = this.loadExcel(excelFile);
            DataSource ds = this.getDataSource(book);
            this.expTableRecordNumByFileInOnePage(book, excelFile, ds);
            logger.info(file.getName() + " has been prcoessd.");
        }
    }

    /**
     * expTableStructByFile
     * 
     * @param book
     * @param ds
     * @return
     * @throws Throwable
     */
    protected Map<String, TableStructExportBean> expTableStructByFile(Workbook book, DataSource ds) throws Throwable {
        List<TableStructExportParam> tblConfigList = getTblConfigInfoForStructExporter(book);
        Map<String, TableStructExportBean> resultMap = new LinkedHashMap<>();
        for (TableStructExportParam tblConfig : tblConfigList) {
            try {
                TableStructExportBean result = tableStructExporter.expTableInfo(tblConfig, ds);
                resultMap.put(result.getTable(), result);
            } catch (Exception e) {
                logger.error(e.getMessage(), e);
            }
        }
        return resultMap;
    }

    /**
     * 创建 Insert SQL 文件。
     * 
     * @throws Exception
     */
    protected void createInsertSqlFile(Workbook book, String excelFile) throws Exception {
        String fileName = excelFile.substring(0, excelFile.lastIndexOf("."));
        String charset = "UTF-8";
        OutputStreamWriter sqlFilePrinter = null;
        try {
            sqlFilePrinter = new OutputStreamWriter(new FileOutputStream(fileName + ".sql"), charset);
            for (int i = 0; i < book.getNumberOfSheets(); i++) {
                Sheet sheet = book.getSheetAt(i);
                if (sheet.getSheetName().startsWith("#")) {
                    continue;
                }
                List<String> sqlList = createInsertSQL(book, excelFile, sheet.getSheetName());

                for (String e : sqlList) {
                    sqlFilePrinter.write(e);
                    sqlFilePrinter.write(LINE_SEPARATOR);
                }
                sqlFilePrinter.write(LINE_SEPARATOR);
                sqlFilePrinter.flush();
            }
        } finally {
            sqlFilePrinter.close();
        }
    }

    /**
     * 创建数据 JSON 文件。
     *
     * @param useCamelCase
     * @throws Exception
     */
    protected void readExcelDataAsJson(Workbook book, String excelFile, Boolean useCamelCase) throws Exception {
        String fileName = excelFile.substring(0, excelFile.lastIndexOf("."));
        String charset = "UTF-8";
        OutputStreamWriter jsonFilePrinter = null;
        try {
            jsonFilePrinter = new OutputStreamWriter(new FileOutputStream(fileName + ".json"), charset);
            for (int i = 0; i < book.getNumberOfSheets(); i++) {
                Sheet sheet = book.getSheetAt(i);
                if (sheet.getSheetName().startsWith("#")) {
                    continue;
                }
                List<String> list = new ArrayList<String>();

                if (useCamelCase == null || useCamelCase) {
                    list.add("// [" + tableDataExporter.toCamelCase(sheet.getSheetName()) + "]");
                    List<String> l = readExcelDataAsJson(book, excelFile, sheet.getSheetName(), true);
                    list.addAll(l);
                }
                if (useCamelCase == null || !useCamelCase) {
                    list.add("// [" + sheet.getSheetName() + "]");
                    List<String> l = readExcelDataAsJson(book, excelFile, sheet.getSheetName(), false);
                    list.addAll(l);
                }
                for (String e : list) {
                    jsonFilePrinter.write(e);
                    jsonFilePrinter.write(LINE_SEPARATOR);
                }
                jsonFilePrinter.write(LINE_SEPARATOR);
                jsonFilePrinter.flush();
            }
        } finally {
            jsonFilePrinter.close();
        }
    }

    /**
     * 创建数据Insert用的SQL语句
     *
     * @param excelFile
     * @param sheetName
     * @return
     * @throws Exception
     */
    protected List<String> createInsertSQL(Workbook book, String excelFile, String sheetName) throws Exception {
        Sheet sheet = book.getSheet(sheetName);
        int x = 1;
        int y = 0;
        String value = getCellValueAsString(sheet, x, y);
        while (!StringUtil.isEmpty(value)) {
            y++;
            value = getCellValueAsString(sheet, x, y);
        }
        int[] targetColIndexes = new int[y];
        for (int c = 0; c < targetColIndexes.length; c++) {
            targetColIndexes[c] = c;
        }
        List<List<Object>> rows = super.readSheetAsObjectList(book, sheet.getSheetName(), targetColIndexes, 0, 65535);
        String table = sheet.getSheetName();
        return tableDataExporter.buildTableInsertSQL(table, rows);
    }

    /**
     * readExcelDataAsJson
     * 
     * @param book
     * @param excelFile
     * @param sheetName
     * @param useCamelCase
     * @return
     * @throws Exception
     */
    protected List<String> readExcelDataAsJson(Workbook book, String excelFile, String sheetName, boolean useCamelCase) throws Exception {
        Sheet sheet = book.getSheet(sheetName);
        int x = 1;
        int y = 0;
        String value = getCellValueAsString(sheet, x, y);
        while (!StringUtil.isEmpty(value)) {
            y++;
            value = getCellValueAsString(sheet, x, y);
        }
        int[] targetColIndexes = new int[y];
        for (int c = 0; c < targetColIndexes.length; c++) {
            targetColIndexes[c] = c;
        }
        List<List<Object>> rows = super.readSheetAsObjectList(new File(excelFile), sheet.getSheetName(), targetColIndexes, 0, 65535);
        String table = sheet.getSheetName();
        return tableDataExporter.buildTableJsonData(table, rows, useCamelCase);
    }

    /**
     * 创建表Size的Excel页(所有表字段导出到一页中)
     * 
     * @throws Throwable
     */
    protected void expTableRecordNumByFileInOnePage(Workbook book, String excelFile, DataSource ds) throws Throwable {
        List<TableStructExportParam> configList = getTblConfigInfoForStructExporter(book);
        List<List<Object>> rows = new ArrayList<>();
        List<Object> row = new ArrayList<>();
        row.add("Schema");
        row.add("Table");
        row.add("Size");
        rows.add(row);
        try {
            for (TableStructExportParam config : configList) {
                row = new ArrayList<>();
                int size = tableStructExporter.expTableRecordNum(config, ds);
                row.add(config.getSchema());
                row.add(config.getTable());
                row.add(size);
                rows.add(row);
            }
            String pageName = tableStructExporter.getTalleRecordNumInOnePageName();
            super.createSheet(book, excelFile, pageName, rows, new CellWriter<Object>() {
                // Title cell style
                CellStyle titleCellstyle = book.createCellStyle();

                @SuppressWarnings("deprecation")
                @Override
                public void write(Cell cell, Object value) {
                    int rowIndex = cell.getRowIndex();
                    if (rowIndex < 1) {
                        titleCellstyle.setBorderTop(BorderStyle.THIN);
                        titleCellstyle.setBorderBottom(BorderStyle.THIN);
                        titleCellstyle.setBorderLeft(BorderStyle.THIN);
                        titleCellstyle.setBorderRight(BorderStyle.THIN);
                        titleCellstyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
                        titleCellstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                        cell.setCellStyle(titleCellstyle);
                    }
                    if (value instanceof Number) {
                        cell.setCellType(CellType.NUMERIC);
                        cell.setCellValue(Double.valueOf(String.valueOf(value)));
                    } else {
                        cell.setCellType(CellType.STRING);
                        cell.setCellValue(String.valueOf(value));
                    }
                }
            });

        } catch (Exception e) {
            logger.error(e.getMessage(), e);
        }
    }

    /**
     * 创建表定义的Excel页
     * 
     * @param tableName
     * @param columnDescList
     */
    protected List<Object> createTitleRow1(String tableName, List<ColumnDescBean> columnDescList) {
        return tableDataExporter.arrayToList(new Object[] { "表描述", "", tableName, "", "", "", "数据库类型", "", "", "", "", "", "", "", "" });
    }

    /**
     * createTitleRow2
     *
     * @param tableName
     * @param columnDescList
     * @return
     */
    protected List<Object> createTitleRow2(String tableName, List<ColumnDescBean> columnDescList) {
        return tableDataExporter.arrayToList(new Object[] { "表名称", "", tableName, "", "", "", "表空间名称", "", "", "", "", "", "", "", "" });
    }

    /**
     * createTitleRow3
     *
     * @param tableName
     * @param columnDescList
     * @return
     */
    protected List<Object> createTitleRow3(String tableName, List<ColumnDescBean> columnDescList) {
        return tableDataExporter.arrayToList(new Object[] { "分表标签", "", "", "", "", "", "默认字段", "", "", "", "", "", "", "", "" });
    }

    /**
     * createTitleRow4
     *
     * @param tableName
     * @param columnDescList
     * @return
     */
    protected List<Object> createTitleRow4(String tableName, List<ColumnDescBean> columnDescList) {
        return tableDataExporter.arrayToList(new Object[] { "", "", "", "", "", "", "", "", "", "", "", "", "", "", "" });
    }

    /**
     * createTitleRow5
     *
     * @param tableName
     * @param columnDescList
     * @return
     */
    protected List<Object> createTitleRow5(String tableName, List<ColumnDescBean> columnDescList) {
        return tableDataExporter
                .arrayToList(new Object[] { "序号", "字段名", "字段ID", "数据类型", "长度", "主键", "非空", "默认值", "格式", "最小值", "最大值", "附加定义", "乐观验证", "抽取类型", "备注" });
    }

    /**
     * createTableDocument
     *
     * @param sheetname
     * @param columnDescList
     */
    protected void createTableDocument(Workbook book, String excelFile, String sheetname, List<ColumnDescBean> columnDescList) {
        List<List<Object>> rows = new ArrayList<List<Object>>();
        List<Object> row1 = createTitleRow1(sheetname, columnDescList);
        List<Object> row2 = createTitleRow2(sheetname, columnDescList);
        List<Object> row3 = createTitleRow3(sheetname, columnDescList);
        List<Object> row4 = createTitleRow4(sheetname, columnDescList);
        List<Object> row5 = createTitleRow5(sheetname, columnDescList);
        if (row1 != null) {
            rows.add(row1);
        }
        if (row2 != null) {
            rows.add(row2);
        }
        if (row3 != null) {
            rows.add(row3);
        }
        if (row4 != null) {
            rows.add(row4);
        }
        if (row5 != null) {
            rows.add(row5);
        }

        rows.addAll(tableDataExporter.createDataRowList(columnDescList, TableStructExporter.ColumnExtractMode.TableDesignMode));

        super.createSheet(book, excelFile, sheetname, rows, new CellWriter<Object>() {
            // Title cell style
            CellStyle titleCellstyle = book.createCellStyle();

            @SuppressWarnings("deprecation")
            @Override
            public void write(Cell cell, Object value) {
                int rowIndex = cell.getRowIndex();
                if (rowIndex < 5) {
                    titleCellstyle.setBorderTop(BorderStyle.THIN);
                    titleCellstyle.setBorderBottom(BorderStyle.THIN);
                    titleCellstyle.setBorderLeft(BorderStyle.THIN);
                    titleCellstyle.setBorderRight(BorderStyle.THIN);
                    titleCellstyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
                    titleCellstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cell.setCellStyle(titleCellstyle);
                }
                if (value instanceof Number) {
                    cell.setCellType(CellType.NUMERIC);
                    cell.setCellValue(Double.valueOf(String.valueOf(value)));
                } else {
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(String.valueOf(value));
                }
            }
        });
    }

    /**
     * getCreateTableDocumentInOneSheetTitleRow
     * 
     * @param tableName
     * @param columnDescList
     * @return
     */
    protected List<Object> getCreateTableDocumentInOneSheetTitleRow(String pageName, List<ColumnDescBean> columnDescList) {
        Object[] title = new Object[] { "Schema", "Table", "NO.", "Field", "Field Desc", "Type Desc", "Length", "Primary Key", "Not Null", "Default", "Type Define" };
        return tableDataExporter.arrayToList(title);
    }

    /**
     * getCreateTableDocumentInOneSheetDataRows
     * 
     * @param tableName
     * @param columnDescList
     * @return
     */
    protected List<List<Object>> getCreateTableDocumentInOneSheetDataRows(String tableName, List<ColumnDescBean> columnDescList) {
        List<List<Object>> rows = new ArrayList<>();
        for (ColumnDescBean tableDesc : columnDescList) {
            List<Object> row = getCreateTableDocumentInOneSheetDataRow(tableName, tableDesc, TableStructExporter.ColumnExtractMode.SimpleMode);
            rows.add(row);
        }
        return rows;
    }

    /**
     * getCreateTableDocumentInOneSheetDataRow
     * 
     * @param row
     * @param tableDescList
     */
    protected List<Object> getCreateTableDocumentInOneSheetDataRow(String tableName, ColumnDescBean columnDescList, ColumnExtractMode mode) {
        return tableDataExporter.createDataRow(columnDescList, TableStructExporter.ColumnExtractMode.SimpleMode);
    }

    /**
     * createTableDocumentInOneSheet
     * 
     * @param book
     * @param excelFile
     * @param tableName
     * @param columnDescList
     */
    protected void createTableDocumentInOneSheet(Workbook book, String excelFile, String pageName, List<ColumnDescBean> columnDescList) {
        List<List<Object>> allRows = new ArrayList<List<Object>>();
        List<Object> title = getCreateTableDocumentInOneSheetTitleRow(pageName, columnDescList);
        allRows.add(title);
        List<List<Object>> rows = getCreateTableDocumentInOneSheetDataRows(pageName, columnDescList);
        allRows.addAll(rows);

        super.createSheet(book, excelFile, pageName, allRows, new CellWriter<Object>() {
            // Title cell style
            CellStyle titleCellstyle = book.createCellStyle();

            @SuppressWarnings("deprecation")
            @Override
            public void write(Cell cell, Object value) {
                int rowIndex = cell.getRowIndex();
                if (rowIndex < 1) {
                    titleCellstyle.setBorderTop(BorderStyle.THIN);
                    titleCellstyle.setBorderBottom(BorderStyle.THIN);
                    titleCellstyle.setBorderLeft(BorderStyle.THIN);
                    titleCellstyle.setBorderRight(BorderStyle.THIN);
                    titleCellstyle.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
                    titleCellstyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                    cell.setCellStyle(titleCellstyle);
                }
                if (value instanceof Number) {
                    cell.setCellType(CellType.NUMERIC);
                    cell.setCellValue(Double.valueOf(String.valueOf(value)));
                } else {
                    cell.setCellType(CellType.STRING);
                    cell.setCellValue(String.valueOf(value));
                }
            }
        });
    }

    /**
     * 导出表数据到Excel文件中。
     * 
     * @throws Throwable
     */
    protected void expTableDataByFile(Workbook book, String excelFile, DataSource ds) throws Throwable {
        List<TableStructExportParam> inputDtoList = getTblConfigInfoForDataExporter(book);
        List<TableStructExportBean> outputDtoList = tableDataExporter.expMultiTableInfo(inputDtoList, ds);

        for (TableStructExportBean outputDto : outputDtoList) {
            createTableDescPage(book, excelFile, outputDto.getTable(), outputDto.getColumnDescList(), outputDto.getRowList());
            logger.info("Finish process of " + outputDto.getTable());
        }
    }

    /**
     * 创建导出表数据的Excel页
     * 
     * @param tableName
     * @param columnDescList
     */
    protected void createTableDescPage(Workbook book,
            String excelFile,
            String tableName,
            List<ColumnDescBean> columnDescList,
            List<Map<String, Object>> dataList) {

        List<List<Object>> rows = new ArrayList<List<Object>>();
        List<Object> row1 = new ArrayList<Object>();
        List<Object> row2 = new ArrayList<Object>();
        List<Object> row3 = new ArrayList<Object>();

        int colIndex = 0;
        for (ColumnDescBean tableDesc : columnDescList) {
            int pkFlag = tableDesc.getIsPrimaryKey() != null ? tableDesc.getIsPrimaryKey() : 0;
            int notNull = tableDesc.getIsNotNull() != null ? tableDesc.getIsNotNull() : 0;

            Map<String, Object> map = new HashMap<String, Object>();
            if (StringUtil.isEmpty(tableDesc.getPropertyName())) {
                map.put("value", tableDesc.getPropertyId());
            } else {
                map.put("value", tableDesc.getPropertyName());
            }
            map.put("rowIndex", 0);
            map.put("colIndex", colIndex);
            map.put("pkFlag", pkFlag);
            map.put("notNull", notNull);
            row1.add(map);

            map = new HashMap<String, Object>();
            map.put("value", tableDesc.getPropertyId());
            map.put("rowIndex", 1);
            map.put("colIndex", colIndex);
            map.put("pkFlag", pkFlag);
            map.put("notNull", notNull);
            row2.add(map);

            map = new HashMap<String, Object>();
            String type = String.valueOf(tableDesc.getDataType());
            if ("MYSQL".equalsIgnoreCase(tableDataExporter.getDatabaseType())) {
                map.put("value", type);
            } else {
                String length = "";
                if (tableDesc.getDataPrecision() != null && tableDesc.getDataScale() != null) {
                    length = "(" + tableDesc.getDataPrecision() + "," + tableDesc.getDataScale() + ")";
                } else if (tableDesc.getDataPrecision() != null) {
                    length = "(" + tableDesc.getDataPrecision() + ")";
                } else if (tableDesc.getDataLength() != null) {
                    length = "(" + tableDesc.getDataLength() + ")";
                }
                map.put("value", type + length);
            }
            map.put("rowIndex", 2);
            map.put("colIndex", colIndex);
            map.put("pkFlag", pkFlag);
            map.put("notNull", notNull);
            row3.add(map);
            colIndex++;
        }
        rows.add(row1);
        rows.add(row2);
        rows.add(row3);

        if (dataList != null) {
            for (Map<String, Object> map : dataList) {
                List<Object> row = new ArrayList<Object>();
                for (Map.Entry<String, Object> entry : map.entrySet()) {
                    String key = entry.getKey();
                    if (key.equalsIgnoreCase("ROWNUM_") && "oracle".equalsIgnoreCase(this.tableDataExporter.getDatabaseType())) {
                        continue;
                    }
                    Object val = entry.getValue();
                    val = val == null ? "" : val;
                    row.add(val);
                }
                rows.add(row);
            }
        }

        super.createSheet(book, excelFile, tableName, rows, new CellWriter<Object>() {
            @SuppressWarnings({ "rawtypes" })
            @Override
            public void write(Cell cell, Object value) {
                if (value instanceof Map) {
                    Object actValue = ((Map) value).get("value");
                    new SimpleCellWriter().write(cell, actValue);

                    int rowIndex = (Integer) ((Map) value).get("rowIndex");
                    int pkFlag = (Integer) ((Map) value).get("pkFlag");
                    int notNull = (Integer) ((Map) value).get("notNull");

                    if (rowIndex < 3) {
                        if (pkFlag == 1) {
                            CellStyle cellstyle1 = book.createCellStyle();
                            cellstyle1.setBorderTop(BorderStyle.THIN);
                            cellstyle1.setBorderBottom(BorderStyle.THIN);
                            cellstyle1.setBorderLeft(BorderStyle.THIN);
                            cellstyle1.setBorderRight(BorderStyle.THIN);
                            cellstyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cellstyle1.setFillForegroundColor(IndexedColors.LIGHT_YELLOW.index);
                            cell.setCellStyle(cellstyle1);
                        } else if (pkFlag != 1 && notNull == 1) {
                            CellStyle cellstyle1 = book.createCellStyle();
                            cellstyle1.setBorderTop(BorderStyle.THIN);
                            cellstyle1.setBorderBottom(BorderStyle.THIN);
                            cellstyle1.setBorderLeft(BorderStyle.THIN);
                            cellstyle1.setBorderRight(BorderStyle.THIN);
                            cellstyle1.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cellstyle1.setFillForegroundColor(IndexedColors.LIGHT_TURQUOISE.index);
                            cell.setCellStyle(cellstyle1);
                        } else {
                            CellStyle cellstyle3 = book.createCellStyle();
                            cellstyle3.setBorderTop(BorderStyle.THIN);
                            cellstyle3.setBorderBottom(BorderStyle.THIN);
                            cellstyle3.setBorderLeft(BorderStyle.THIN);
                            cellstyle3.setBorderRight(BorderStyle.THIN);
                            cellstyle3.setFillPattern(FillPatternType.SOLID_FOREGROUND);
                            cellstyle3.setFillForegroundColor(IndexedColors.LIGHT_GREEN.index);
                            cell.setCellStyle(cellstyle3);
                        }
                    }
                } else {
                    new SimpleCellWriter().write(cell, value);
                }
            }
        });
    }

    /**
     * 读取表名列表
     * 
     * @return
     */
    protected List<TableStructExportParam> getTblConfigInfoForDataExporter(Workbook book) {
        List<TableStructExportParam> configList = new ArrayList<>();
        Sheet sheet = book.getSheet("#TBLCONFIG");
        Coordinate coordinate = lookupDataCell(sheet, "SET SCHEMA");
        int row = coordinate.x + 1;
        int col = coordinate.y;

        String schema = null;
        String table = null;
        String tableComment = null;
        do {
            schema = getCellValueAsString(sheet, row, col).trim();
            table = getCellValueAsString(sheet, row, col + 1).trim();
            String condition = getCellValueAsString(sheet, row, col + 2).trim();
            String orderby = getCellValueAsString(sheet, row, col + 3).trim();
            String limit = getCellValueAsString(sheet, row, col + 4).trim();
            tableComment = getCellValueAsString(sheet, row, col + 2).trim();
            int pageNum = 1;
            int pageSize = 1000;
            if (!StringUtil.isEmpty(limit) && limit.contains(",")) {
                String[] arr = limit.split(",");
                pageNum = Integer.parseInt(arr[0].trim());
                pageSize = Integer.parseInt(arr[1].trim());
            } else if (!StringUtil.isEmpty(limit)) {
                pageSize = Integer.parseInt(limit);
            }
            TableStructExportParam config = new TableStructExportParam();
            config.setSchema(schema);
            config.setTable(table);
            config.setTableComment(tableComment);
            config.setWhereCondition(condition);
            config.setOrderByCondition(orderby);
            config.setPageNum(pageNum);
            config.setPageSize(pageSize);
            if (StringUtil.isNotEmpty(schema) && StringUtil.isNotEmpty(table)) {
                configList.add(config);
            }
            row++;
        } while (StringUtil.isNotEmpty(schema) && StringUtil.isNotEmpty(table));

        return configList;
    }

    /**
     * getTblConfigInfoForStructExporter
     * 
     * @param book
     * @return
     */
    protected List<TableStructExportParam> getTblConfigInfoForStructExporter(Workbook book) {
        List<TableStructExportParam> configList = new ArrayList<>();
        Sheet sheet = book.getSheet("#TBLCONFIG");
        Coordinate coordinate = lookupDataCell(sheet, "SET SCHEMA");
        int row = coordinate.x + 1;
        int col = coordinate.y;

        String schema = null;
        String table = null;
        String tableComment = null;
        do {
            schema = getCellValueAsString(sheet, row, col).trim();
            table = getCellValueAsString(sheet, row, col + 1).trim();
            tableComment = getCellValueAsString(sheet, row, col + 2).trim();
            TableStructExportParam config = new TableStructExportParam();
            config.setSchema(schema);
            config.setTable(table);
            config.setTableComment(tableComment);
            if (StringUtil.isNotEmpty(schema) && StringUtil.isNotEmpty(table)) {
                configList.add(config);
            }
            row++;
        } while (StringUtil.isNotEmpty(schema) && StringUtil.isNotEmpty(table));

        return configList;
    }

    /**
     * 取得数据库数据源
     * 
     * @return
     * @throws Exception
     */
    protected DataSource getDataSource(Workbook book) throws Exception {
        Sheet sheet = book.getSheet("#DBCONFIG");
        Coordinate coordinate = lookupDataCell(sheet, "DATABASE DRIVER");
        String driver = getCellValueAsString(sheet, coordinate.x, coordinate.y + 1);
        coordinate = lookupDataCell(sheet, "DATABASE URL");
        String url = getCellValueAsString(sheet, coordinate.x, coordinate.y + 1);
        coordinate = lookupDataCell(sheet, "DATABASE USER");
        String user = getCellValueAsString(sheet, coordinate.x, coordinate.y + 1);
        coordinate = lookupDataCell(sheet, "DATABASE PASSWORD");
        String password = getCellValueAsString(sheet, coordinate.x, coordinate.y + 1);
        return buildDataSource(driver, url, user, password);
    }

    /**
     * buildDataSource
     * 
     * @param driver
     * @param url
     * @param user
     * @param password
     * @return
     * @throws Exception
     */
    public static DataSource buildDataSource(String driver, String url, String user, String password) throws Exception {
        HikariConfig hc = new HikariConfig();
        hc.setJdbcUrl(url);
        hc.setDriverClassName(driver);
        hc.setUsername(user);
        hc.setPassword(password);

        DataSource ds = new HikariDataSource(hc);
        return ds;
    }

    /**
     * getFiles2
     *
     * @param inputPath
     * @param tarExts
     * @return
     * @throws Exception
     */
    public static File[] getFiles(String inputPath, String[] tarExts) throws Exception {
        File[] files = null;
        File path = new File(inputPath);
        if (path.isDirectory()) {
            files = path.listFiles(new FileFilter() {
                @Override
                public boolean accept(File e) {
                    String name = e.getName().toLowerCase();
                    return isTargetFile(name, tarExts);
                }
            });
        } else if (path.isFile()) {
            String name = path.getName().toLowerCase();
            if (isTargetFile(name, tarExts)) {
                files = new File[] { path };
            }
        }
        return files;
    }

    /**
     * getExcelFiles
     *
     * @param inputPath
     * @return
     * @throws Exception
     */
    public static File[] getExcelFiles(String inputPath) throws Exception {
        return getFiles(inputPath, new String[] { "xls", "xlsx", "xlsm" });
    }

    /**
     * isTargetFile
     *
     * @param tarExts
     * @return
     */
    public static boolean isTargetFile(String name, String[] tarExts) {
        for (String tarExt : tarExts) {
            if (!tarExt.startsWith(".")) {
                tarExt = "." + tarExt;
            }
            if (name.endsWith(tarExt)) {
                return true;
            }
        }
        return false;
    }
}
